﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

/// <summary>
///操作数据库
/// </summary>
public class sqlDataBase
{
    protected SqlConnection conn;
    protected SqlCommand cmd;
    protected SqlDataAdapter da;
    protected SqlTransaction transact;
    protected SqlDataReader dr;
    protected DataSet ds;

    //获取数据库连接字符串；
    protected string _connstring = ConfigurationManager.ConnectionStrings["smssConnectionString"].ConnectionString;

    /// <summary>
    /// 初始化conn和cmd
    /// </summary>
	public sqlDataBase()
	{
		//
		//TODO: 在此处添加构造函数逻辑
		//
        conn = new SqlConnection();
        conn.ConnectionString = _connstring;
        cmd = new SqlCommand();
        cmd.Connection = conn;
	}

    /// <summary>
    /// 无返回值的执行SQL命令
    /// </summary>
    /// <param name="sqlstring">操作命令</param>
    public  void runnosql(string sqlstring)
    {
        cmd.CommandText = sqlstring;
        conn.Open();
        cmd.ExecuteNonQuery();
        if (conn.State == ConnectionState.Open)
        {
            conn.Close();
        }
    }

    /// <summary>
    /// 执行SQL命令，并返回第一行第一列数据
    /// </summary>
    /// <param name="sqlstring">SQL命令</param>
    /// <returns></returns>
    public object runsql(string sqlstring)
    {
        object ret=null;
        cmd.CommandText = sqlstring;
        conn.Open();
        ret = cmd.ExecuteScalar();
        conn.Close();
        return ret; 
    }

    /// <summary>
    /// 执行SQL并返回受影响的行数
    /// </summary>
    /// <param name="sqlstring">SQL命令</param>
    /// <returns></returns>
   public int runsqlReturn(string sqlstring)
    {
        cmd.CommandText = sqlstring;
        conn.Open();
        int count = cmd.ExecuteNonQuery();
        conn.Close();
        return count;
    }

    /// <summary>
    /// 获取DataSet集
    /// </summary>
    /// <param name="sqlstring">SQL命令</param>
    /// <param name="tableName">表名</param>
    /// <returns></returns>
    public DataSet GetDS(string sqlstring,string tableName)
    {
        conn.Open();
        da = new SqlDataAdapter(sqlstring,conn);
        da.Fill(ds,tableName);
        conn.Close();
        return ds;
    }

    /// <summary>
    /// 获取记录表
    /// </summary>
    /// <param name="sqlstring">SQL命令</param>
    /// <param name="tableName">表名</param>
    /// <returns></returns>
    public DataTable GetDT(string sqlstring,string tableName)
    {
         DataSet ds= GetDS(sqlstring,tableName);
         return ds.Tables[tableName];
    }

    /// <summary>
    /// 返回SqlDataReader对象
    /// </summary>
    /// <param name="sqlstring"></param>
    /// <returns></returns>
    public SqlDataReader getRead(string sqlstring)
    {
        cmd.CommandText = sqlstring;
        conn.Open();
        dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//关闭dr时也会关闭conn；
        return dr;
    }
}
